Amazon Redshift DB開発者ガイド – データのロード処理(6).テーブルの更新について

Amazon Redshift DB開発者ガイド

#データベース

#Amazon Redshift

#AWS

しんや

2013.08.30

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

最近弊社AWSチームのメンバーが諸々の事情で別室(a.k.a.ショムニ部屋命名者はこの御方)にメンバー丸ごとお引越しをしたのですが、先日この部屋にAppleTVが設置され、皆さんこぞって思い思いの楽しみ方をしつつ仕事に全力投球している今日この頃、皆様いかがお過ごしでしょうか。しんやです。

データロード処理についてはこれまで5本エントリを書いてきましたが、当エントリはその続編、6本目となります。テーマは『テーブルの更新』です。データロードは基本的に"登録"ですが、局面として既存投入済データを更新したいというケースも勿論ある訳で、その辺りについて色々見ていこうと思います。ボリューム的にはまだまだ少ない感じはありますが、有用な情報が見つかったら適宜こちらのエントリに追記更新をして行きたいと思います。

DMLコマンドによるテーブルの更新

Amazon Redshiftは、テーブル内の行を変更する為に使用出来る、標準的なデータ操作言語(DML)、コマンド(INSERT, UPDATE, DELETE)をサポートしています。また、高速にバルク削除を実行する為のTRUNCATEコマンドを使用する事が出来ます。

注意：
大量データをロードする際は、COPYコマンドを使用する事を強くお勧めします。
個々のINSERT文を使うと、とんでもなく時間が掛かる可能性があります。
別の方法として、もしあなたのデータがAmazon Redshiftに存在するのであれば、
パフォーマンスを向上させる為にSELECT INTO … INSERTやCREATE TABLE ASを使って
テーブルに挿入したり、作成を行います。
詳しくは、INSERTやCREATE TABLE ASをご参照ください。

もしあなたがINSERTやUPDATE, DELETEをテーブルに対して実行した際に、実行前と行数や変更内容が大きく変わるようであれば、作業が終わったらテーブルに対してANALYZEやVACUUMコマンドを実行します。

アプリケーションに於いて、小さな変更が時間を掛けて蓄積した場合も、同様にANALYZEやVACUUMコマンドを定期的に実行する事をお奨めします。詳細については、テーブル分析とテーブルのバキューム処理をご参照ください。

更新と新規データの追加

ステージングテーブルを用いて『更新と追加(これは、UPSERTとしても知られていますが)』の組み合わせを使う事により、既存のテーブルに効率的に新しいデータを追加する事が出来ます。

Amazon Redshiftは単一のデータソースからINSERTとUPDATEを行うためのUPSERTコマンドをサポートしていませんが、ステージングテーブルにデータをロードしてからUPDATE文及びINSERT文のターゲットとなっているステージングテーブルを結合する事により、UPSERT操作を効果的に行なう事が出来ます。

ステージングテーブルを使用してUPSERTの操作を実行するには、以下の手順に従います。

新たなデータを保持する為のステージングテーブルを作成します。
ターゲットのテーブルが使用しているものと同じ分散キー(distkey)を、ステージングテーブルに対しても同様に指定します。そうする事で、2つのテーブル間の結合が併置されるようになります。例えば、ターゲットのテーブルが分散キーとして外部キー列を使用している場合、ステージングテーブルの分散キーも同じくその外部キー列を指定します。
UPDATE及びINSERT文では、併置結合を達成する為の主キーの結合に加えて、外部キー列間に冗長な結合述部を追加します。しかしながら、これはあなたが結合を連結する為に使用している列が、UPSERT操作の一部として更新されていない場合のみ動作します。例えば、あなたが書いているWHERE句では、普通に主キー列を結合しているものとしましょう。
```
WHERE target.primaryKey = staging.primaryKey 
```
- また、以下の例に示すように、分散キーを結合する為の冗長な結合を追加します。
```
WHERE target.primaryKey = staging.primaryKey AND target.distKey = staging.distKey 
```
もしターゲットとなるテーブルがタイムスタンプでソートされ、あなたの行った更新が特定の閾(しきい)値よりも過去に行われていない場合は、ターゲットテーブル上の範囲制限のスキャンを活用する為に述語(target.timestamp > 閾値)を追加します。

以下の例では、アップサートの動作を示します。

TARGETはあなたが更新したいテーブル、STAGINGはあなたがアップサートを行いたいデータを含むテーブルである、と仮定しましょう。両方のテーブルの分散キーによる冗長な結合述部(AND target.distKey = s.distKey)に注意してください。

このSQL文では、ターゲットテーブルに既に存在しているレコードを、ステージングテーブルの新しい値で更新しています。条件はターゲットテーブルとステージングテーブル間で条件にマッチするもの全てに対して、です。

UPDATE target SET col1 = s.col1, col2 = s.col2
	FROM staging s
	WHERE target.primaryKey = s.primaryKey AND target.distKey = s.distKey;

次の以下SQL文では、ターゲットテーブルにはまだ存在していないデータをステージングテーブルから新しい行として挿入しています。

INSERT INTO target 
SELECT s.* FROM staging s LEFT JOIN target t
ON s.primaryKey = t.primaryKey AND s.distKey = t.distKey
WHERE t.primaryKey IS NULL;

その他

Amazon Redshiftに於けるデータの更新については、以下エントリ等も参考になります。データ量も莫大な規模に増えて行く中で、更新(UPDATE)処理にも迅速且つ効率的な処理が求められていく事は必至ですので、この辺りもよりベター・ベストな方法を模索して行きたいところです。

KLab-データ分析グループのblog : Amazon Redshiftで高速にINSERT + UPDATEを行なう

Amazon Redshift DB開発者ガイド – データのロード処理(6).テーブルの更新について

目次

DMLコマンドによるテーブルの更新

更新と新規データの追加

その他

関連記事

主なカテゴリ

AWS

おすすめ

プロダクト

コンテンツ

お問い合わせ

運営会社